package org.uninova.mobis.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.uninova.mobis.pojos.CarSharingRoute;
import org.uninova.mobis.pojos.CarSharingUser;
import org.uninova.mobis.pojos.Coordinate;
import org.uninova.mobis.utils.DBUtilsImpl;

public class CarSharingDBConnector {

	public CarSharingDBConnector() {}
	
	public CarSharingUser createUser(DBUtilsImpl dbUtils, Connection con, CarSharingUser user) {
		String sqlCreateUser = "INSERT INTO mobis_car_sharing.user (user_name,user_email,user_phone) VALUES ('" + user.getUserName() + "','" + user.getUserEmail() + "','" + user.getUserPhone() + "')" ;
		dbUtils.modifyDB(con, sqlCreateUser) ;
		user = this.getUser(dbUtils, con, user.getUserName(), user.getUserEmail(), user.getUserPhone()) ;
		return user ;
	}
	
	public CarSharingUser getUser(DBUtilsImpl dbUtils, Connection con, String userName, String userEmail, String userPhone) {
		String sqlGetUser = "SELECT id_user,user_name,user_email,user_phone FROM mobis_car_sharing.user WHERE " ;
		
		if (userName != null && !userName.equals("")) {
			sqlGetUser += "user_name=" + userName ;
		}
		else if (userEmail != null && !userEmail.equals("")) {
			sqlGetUser += "user_email=" + userEmail ;
		}
		else if (userPhone != null && !userPhone.equals("")) {
			sqlGetUser += "user_phone=" + userPhone ;
		}
		ResultSet set = dbUtils.queryDB(con, sqlGetUser) ;
		try {
			if (set.next()) {
				CarSharingUser user = new CarSharingUser() ;
				user.setUserId(set.getLong("id_user")) ;
				user.setUserName(set.getString("user_name")) ;
				user.setUserEmail(set.getString("user_email")) ;
				user.setUserPhone(set.getString("user_phone")) ;
				user.setRoutes(this.getRoutes(dbUtils, con, 0, user.getUserId())) ;
				return user ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null ;
	}
	
	public int deleteUser(DBUtilsImpl dbUtils, Connection con, String userName, String userEmail, String userPhone) {
		String sqlDeleteUser = "DELETE FROM mobis_car_sharing.user WHERE " ;
		String sqlGetUserId = "SELECT id_user FROM mobis_car_sharing.user WHERE " ;
		if (userName != null && !userName.equals("")) {
			sqlDeleteUser += "user_name=" + userName ;
			sqlGetUserId += "user_name=" + userName ;
		}
		else if (userEmail != null && !userEmail.equals("")) {
			sqlDeleteUser += "user_email=" + userEmail ;
			sqlGetUserId += "user_email=" + userEmail ;
		}
		else if (userPhone != null && !userPhone.equals("")) {
			sqlDeleteUser += "user_phone=" + userPhone ;
			sqlGetUserId += "user_phone=" + userPhone ;
		}
		
		ResultSet set = dbUtils.queryDB(con, sqlGetUserId) ;
		try {
			set.next() ;
			long userId = set.getLong("id_user") ;
			this.deleteRoutes(dbUtils, con, 0, userId) ;
			return dbUtils.modifyDB(con, sqlDeleteUser) ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1 ;
	}
	
	public int createRoute(DBUtilsImpl dbUtils, Connection con, CarSharingRoute route) {
		String sqlCreateRoute = "INSERT INTO mobis_car_sharing.route (id_user, with_car, code) VALUES (" + route.getUserId()+ "," + route.getGotCar() + ",'" + route.getCode() + "')" ;
		dbUtils.modifyDB(con, sqlCreateRoute) ;
		
		String sqlGetRouteId = "SELECT id_route FROM mobis_car_sharing.route WHERE code=" + route.getCode() ;
		ResultSet set = dbUtils.queryDB(con, sqlGetRouteId) ;
		try {
			set.next() ;
			long routeId = set.getLong("id_route") ;
			Coordinate coord ;
			for (int i = 0; i < route.getCoords().size(); i++) {
				coord = route.getCoords().get(i) ;
				this.createPoint(dbUtils, con, routeId, coord) ;
			}
		return 1 ;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1 ;
	}
	
	public ArrayList<CarSharingRoute> getRoutes(DBUtilsImpl dbUtils, Connection con, long routeId, long userId) {
		String sqlGetRoute = "SELECT id_route, id_user, with_car, code FROM mobis_car_sharing.route" ;
		CarSharingRoute route = new CarSharingRoute() ;
		
		if (routeId > 0) {
			sqlGetRoute += " WHERE id_route=" + routeId ;
		}
		else if (userId > 0) {
			sqlGetRoute += " WHERE id_user=" + userId ;
		}
		
		ArrayList<CarSharingRoute> routes = new ArrayList<CarSharingRoute>() ;
		
		try {
			ResultSet set = dbUtils.queryDB(con, sqlGetRoute) ;
			while (set.next()) {
				route.setRouteId(set.getLong("id_route")) ;
				route.setUserId(set.getLong("id_user")) ;
				route.setCode(set.getString("code")) ;
				route.setGotCar(set.getInt("with_car")) ;
				route.setCoords(this.getPoints(dbUtils, con, route.getRouteId())) ;
				routes.add(route) ;
			}
			return routes ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null ;
	}
	
	public int deleteRoutes(DBUtilsImpl dbUtils, Connection con, long routeId, long userId) {
		this.deletePoints(dbUtils, con, routeId) ;
		String sqlDeleteRoute = "DELETE FROM mobis_car_sharing.route WHERE id_route=" + routeId ;
		
		if (routeId > 0) {
			sqlDeleteRoute += "id_route=" + routeId ;
		}
		else if (userId > 0) {
			sqlDeleteRoute += "id_user=" + userId ;
		}
		
		return dbUtils.modifyDB(con, sqlDeleteRoute) ;
	}
	
	public void createPoint(DBUtilsImpl dbUtils, Connection con, long routeId, Coordinate coord) {
		String sqlCreatePoint = "INSERT INTO mobis_car_sharing.point (point_lat, point_lng, id_route) VALUES (" + coord.lat + "," + coord.lng + "," + routeId + ")" ;
		dbUtils.modifyDB(con, sqlCreatePoint) ;
	}
	
	public ArrayList<Coordinate> getPoints(DBUtilsImpl dbUtils, Connection con, long routeId) {
		String sqlGetPoint = "SELECT point_lat, point_lng FROM mobis_car_sharing.point WHERE id_route=" + routeId ;
		ArrayList<Coordinate> coords = new ArrayList<Coordinate>() ;
		Coordinate coord ;
		ResultSet set = dbUtils.queryDB(con, sqlGetPoint) ;
		try {
			while (set.next()) {
				coord = new Coordinate(set.getDouble("point_lat"), set.getDouble("point_lng")) ;
				coords.add(coord) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return coords ;
	}
	
	public int deletePoints(DBUtilsImpl dbUtils, Connection con, long routeId) {
		String sqlDeletePoint = "DELETE FROM mobis_car_sharing.point WHERE id_route=" + routeId ;
		return dbUtils.modifyDB(con, sqlDeletePoint) ;
	}
}
